{
 "cells": [
  {
   "cell_type": "code",
   "id": "initial_id",
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    },
    "ExecuteTime": {
     "end_time": "2025-09-16T06:40:10.980796Z",
     "start_time": "2025-09-16T06:40:10.972828Z"
    }
   },
   "source": [
    "from pyspark.sql.functions import *\n",
    "from pyspark.sql.session import SparkSession\n",
    "from pyspark.sql.window import Window\n",
    "\n",
    "# 1、创建spark sql执行环境\n",
    "spark = SparkSession \\\n",
    "    .builder \\\n",
    "    .master(\"local\") \\\n",
    "    .appName(\"dsl\") \\\n",
    "    .config(\"spark.sql.shuffle.partitions\", 1) \\\n",
    "    .getOrCreate()"
   ],
   "outputs": [],
   "execution_count": 91
  },
  {
   "cell_type": "code",
   "id": "fec31b713a645ef9",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-16T02:57:21.489866Z",
     "start_time": "2025-09-16T02:57:19.650613Z"
    }
   },
   "source": [
    "# 2、读取CSV格式的数据\n",
    "students_df = spark \\\n",
    "    .read \\\n",
    "    .format(\"csv\") \\\n",
    "    .option(\"sep\", \",\") \\\n",
    "    .schema(\"id STRING,name STRING,age INT,sex STRING,clazz STRING\") \\\n",
    "    .load(\"../../data/students.txt\")"
   ],
   "outputs": [],
   "execution_count": 4
  },
  {
   "cell_type": "code",
   "id": "a665bc5e490c25a2",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-16T02:58:10.323405Z",
     "start_time": "2025-09-16T02:58:10.031318Z"
    }
   },
   "source": [
    "# 1、show\n",
    "students_df.show()\n",
    "students_df.show(n=100)\n",
    "students_df.show(truncate=False)\n",
    "\n",
    "# 1、select\n",
    "students_df.select(\"id\", \"name\").show()"
   ],
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------+---+---+--------+\n",
      "|        id|  name|age|sex|   clazz|\n",
      "+----------+------+---+---+--------+\n",
      "|1500100001|施笑槐| 22| 女|文科六班|\n",
      "|1500100002|吕金鹏| 24| 男|文科七班|\n",
      "|1500100003|单乐蕊| 22| 女|理科六班|\n",
      "|1500100004|葛德曜| 24| 男|理科三班|\n",
      "|1500100005|宣谷芹| 22| 女|理科五班|\n",
      "|1500100006|边昂雄| 21| 男|理科二班|\n",
      "|1500100007|尚孤风| 23| 女|文科六班|\n",
      "|1500100008|符半双| 22| 女|理科六班|\n",
      "|1500100009|沈德昌| 21| 男|理科一班|\n",
      "|1500100010|羿彦昌| 23| 男|理科六班|\n",
      "|1500100011|宰运华| 21| 男|理科三班|\n",
      "|1500100012|梁易槐| 21| 女|理科一班|\n",
      "|1500100013|逯君昊| 24| 男|文科二班|\n",
      "|1500100014|羿旭炎| 23| 男|理科五班|\n",
      "|1500100015|宦怀绿| 21| 女|理科一班|\n",
      "|1500100016|潘访烟| 23| 女|文科一班|\n",
      "|1500100017|高芷天| 21| 女|理科五班|\n",
      "|1500100018|骆怜雪| 21| 女|文科六班|\n",
      "|1500100019|娄曦之| 24| 男|理科三班|\n",
      "|1500100020|杭振凯| 23| 男|理科四班|\n",
      "+----------+------+---+---+--------+\n",
      "only showing top 20 rows\n",
      "\n",
      "+----------+------+---+---+--------+\n",
      "|        id|  name|age|sex|   clazz|\n",
      "+----------+------+---+---+--------+\n",
      "|1500100001|施笑槐| 22| 女|文科六班|\n",
      "|1500100002|吕金鹏| 24| 男|文科七班|\n",
      "|1500100003|单乐蕊| 22| 女|理科六班|\n",
      "|1500100004|葛德曜| 24| 男|理科三班|\n",
      "|1500100005|宣谷芹| 22| 女|理科五班|\n",
      "|1500100006|边昂雄| 21| 男|理科二班|\n",
      "|1500100007|尚孤风| 23| 女|文科六班|\n",
      "|1500100008|符半双| 22| 女|理科六班|\n",
      "|1500100009|沈德昌| 21| 男|理科一班|\n",
      "|1500100010|羿彦昌| 23| 男|理科六班|\n",
      "|1500100011|宰运华| 21| 男|理科三班|\n",
      "|1500100012|梁易槐| 21| 女|理科一班|\n",
      "|1500100013|逯君昊| 24| 男|文科二班|\n",
      "|1500100014|羿旭炎| 23| 男|理科五班|\n",
      "|1500100015|宦怀绿| 21| 女|理科一班|\n",
      "|1500100016|潘访烟| 23| 女|文科一班|\n",
      "|1500100017|高芷天| 21| 女|理科五班|\n",
      "|1500100018|骆怜雪| 21| 女|文科六班|\n",
      "|1500100019|娄曦之| 24| 男|理科三班|\n",
      "|1500100020|杭振凯| 23| 男|理科四班|\n",
      "|1500100021|连鸿晖| 22| 男|理科四班|\n",
      "|1500100022|薄运珧| 23| 男|文科四班|\n",
      "|1500100023|东鸿畴| 23| 男|理科二班|\n",
      "|1500100024|湛慕卉| 22| 女|文科二班|\n",
      "|1500100025|翁飞昂| 22| 男|文科四班|\n",
      "|1500100026|向鹏池| 21| 男|理科四班|\n",
      "|1500100027|路辰锟| 21| 男|文科四班|\n",
      "|1500100028|幸浩邈| 24| 男|理科五班|\n",
      "|1500100029|滕旭炎| 21| 男|理科二班|\n",
      "|1500100030|姚依丝| 21| 女|理科六班|\n",
      "|1500100031|麻智刚| 24| 男|文科六班|\n",
      "|1500100032|雍静枫| 24| 女|文科二班|\n",
      "|1500100033|桑昆峰| 24| 男|理科三班|\n",
      "|1500100034|薛鸿朗| 24| 男|理科五班|\n",
      "|1500100035|包瀚玥| 24| 男|理科四班|\n",
      "|1500100036|阮旭炎| 22| 男|文科二班|\n",
      "|1500100037|顾向松| 22| 女|文科一班|\n",
      "|1500100038|蓟振强| 21| 男|理科五班|\n",
      "|1500100039|窦孤风| 22| 女|文科六班|\n",
      "|1500100040|纪映之| 24| 女|文科四班|\n",
      "|1500100041|傅景天| 24| 男|理科四班|\n",
      "|1500100042|麻旭尧| 24| 男|文科四班|\n",
      "|1500100043|晏昆鹏| 24| 男|理科六班|\n",
      "|1500100044|田天风| 24| 女|文科二班|\n",
      "|1500100045|仇运晟| 21| 男|理科一班|\n",
      "|1500100046|靳香巧| 22| 女|理科五班|\n",
      "|1500100047|秋旭尧| 23| 男|文科六班|\n",
      "|1500100048|从子辰| 21| 男|理科六班|\n",
      "|1500100049|籍涵煦| 21| 男|理科六班|\n",
      "|1500100050|方小蕾| 22| 女|文科五班|\n",
      "|1500100051|江寄容| 24| 女|文科五班|\n",
      "|1500100052|居初兰| 23| 女|理科二班|\n",
      "|1500100053|扈鸿朗| 21| 男|文科五班|\n",
      "|1500100054|梅元容| 21| 女|理科二班|\n",
      "|1500100055|卫鸿熙| 24| 男|文科一班|\n",
      "|1500100056|古鸿信| 21| 男|理科二班|\n",
      "|1500100057|经沛白| 24| 女|文科四班|\n",
      "|1500100058|薄谷蕊| 23| 女|理科三班|\n",
      "|1500100059|席昌燎| 24| 男|理科二班|\n",
      "|1500100060|阮迎荷| 24| 女|文科四班|\n",
      "|1500100061|路紫萱| 21| 女|文科四班|\n",
      "|1500100062|空芷荷| 24| 女|文科二班|\n",
      "|1500100063|申半双| 24| 女|文科四班|\n",
      "|1500100064|汲辰沛| 22| 男|理科六班|\n",
      "|1500100065|蓬昂雄| 22| 男|理科二班|\n",
      "|1500100066|惠耘涛| 22| 男|文科三班|\n",
      "|1500100067|广浦泽| 22| 男|文科五班|\n",
      "|1500100068|宣南蓉| 23| 女|理科一班|\n",
      "|1500100069|束采波| 22| 女|文科一班|\n",
      "|1500100070|濮佑运| 22| 男|理科一班|\n",
      "|1500100071|祖昌勋| 22| 男|理科四班|\n",
      "|1500100072|段怀梦| 23| 女|理科二班|\n",
      "|1500100073|滕旭炎| 23| 男|文科二班|\n",
      "|1500100074|史鹏煊| 22| 男|理科六班|\n",
      "|1500100075|谭春蕾| 21| 女|文科二班|\n",
      "|1500100076|竺乐蓉| 23| 女|理科五班|\n",
      "|1500100077|钮元蝶| 24| 女|理科三班|\n",
      "|1500100078|干安珊| 24| 女|理科四班|\n",
      "|1500100079|曾辰宇| 22| 男|文科六班|\n",
      "|1500100080|巫景彰| 21| 男|理科五班|\n",
      "|1500100081|嵇平安| 21| 女|理科三班|\n",
      "|1500100082|凌思菱| 22| 女|文科一班|\n",
      "|1500100083|雍惜玉| 22| 女|文科二班|\n",
      "|1500100084|应景平| 22| 男|理科三班|\n",
      "|1500100085|彭旭东| 21| 男|文科二班|\n",
      "|1500100086|祖昆锐| 22| 男|理科一班|\n",
      "|1500100087|童鸿禧| 23| 男|理科五班|\n",
      "|1500100088|田德明| 21| 男|文科三班|\n",
      "|1500100089|蒙依丝| 22| 女|文科五班|\n",
      "|1500100090|富寄风| 21| 女|文科五班|\n",
      "|1500100091|蔺水风| 22| 女|文科一班|\n",
      "|1500100092|竺惜玉| 21| 女|理科四班|\n",
      "|1500100093|裴依秋| 22| 女|文科二班|\n",
      "|1500100094|韩醉柳| 22| 女|文科一班|\n",
      "|1500100095|尹宛秋| 23| 女|文科三班|\n",
      "|1500100096|沈曜瑞| 21| 男|理科三班|\n",
      "|1500100097|叶德昌| 24| 男|文科二班|\n",
      "|1500100098|卞俊晖| 22| 男|理科一班|\n",
      "|1500100099|成曜文| 21| 男|文科五班|\n",
      "|1500100100|宁昊磊| 22| 男|文科六班|\n",
      "+----------+------+---+---+--------+\n",
      "only showing top 100 rows\n",
      "\n",
      "+----------+------+---+---+--------+\n",
      "|id        |name  |age|sex|clazz   |\n",
      "+----------+------+---+---+--------+\n",
      "|1500100001|施笑槐|22 |女 |文科六班|\n",
      "|1500100002|吕金鹏|24 |男 |文科七班|\n",
      "|1500100003|单乐蕊|22 |女 |理科六班|\n",
      "|1500100004|葛德曜|24 |男 |理科三班|\n",
      "|1500100005|宣谷芹|22 |女 |理科五班|\n",
      "|1500100006|边昂雄|21 |男 |理科二班|\n",
      "|1500100007|尚孤风|23 |女 |文科六班|\n",
      "|1500100008|符半双|22 |女 |理科六班|\n",
      "|1500100009|沈德昌|21 |男 |理科一班|\n",
      "|1500100010|羿彦昌|23 |男 |理科六班|\n",
      "|1500100011|宰运华|21 |男 |理科三班|\n",
      "|1500100012|梁易槐|21 |女 |理科一班|\n",
      "|1500100013|逯君昊|24 |男 |文科二班|\n",
      "|1500100014|羿旭炎|23 |男 |理科五班|\n",
      "|1500100015|宦怀绿|21 |女 |理科一班|\n",
      "|1500100016|潘访烟|23 |女 |文科一班|\n",
      "|1500100017|高芷天|21 |女 |理科五班|\n",
      "|1500100018|骆怜雪|21 |女 |文科六班|\n",
      "|1500100019|娄曦之|24 |男 |理科三班|\n",
      "|1500100020|杭振凯|23 |男 |理科四班|\n",
      "+----------+------+---+---+--------+\n",
      "only showing top 20 rows\n",
      "\n",
      "+----------+------+\n",
      "|        id|  name|\n",
      "+----------+------+\n",
      "|1500100001|施笑槐|\n",
      "|1500100002|吕金鹏|\n",
      "|1500100003|单乐蕊|\n",
      "|1500100004|葛德曜|\n",
      "|1500100005|宣谷芹|\n",
      "|1500100006|边昂雄|\n",
      "|1500100007|尚孤风|\n",
      "|1500100008|符半双|\n",
      "|1500100009|沈德昌|\n",
      "|1500100010|羿彦昌|\n",
      "|1500100011|宰运华|\n",
      "|1500100012|梁易槐|\n",
      "|1500100013|逯君昊|\n",
      "|1500100014|羿旭炎|\n",
      "|1500100015|宦怀绿|\n",
      "|1500100016|潘访烟|\n",
      "|1500100017|高芷天|\n",
      "|1500100018|骆怜雪|\n",
      "|1500100019|娄曦之|\n",
      "|1500100020|杭振凯|\n",
      "+----------+------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "execution_count": 8
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-16T03:07:56.941216Z",
     "start_time": "2025-09-16T03:07:56.633576Z"
    }
   },
   "cell_type": "code",
   "source": [
    "students_df.show()\n",
    "# 2、select\n",
    "students_df.select(\"id\", \"name\").show(10)\n",
    "# 进行计算\n",
    "# 通过DF获取字段\n",
    "students_df.select(\"id\", (students_df.age + 100).alias(\"age\")).show(10)\n",
    "# 通过col获取字段\n",
    "students_df.select(\"id\", (col(\"age\") + 20).alias(\"age\")).show(10)\n",
    "# selectExpr：闯入一个sql表达式\n",
    "students_df.selectExpr(\"id\", \"abs(age + 100) as age\").show()\n",
    "students_df.select(\"id\", abs(\"age\").alias(\"age\")).show()\n"
   ],
   "id": "6570cc8675263ae3",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------+---+---+--------+\n",
      "|        id|  name|age|sex|   clazz|\n",
      "+----------+------+---+---+--------+\n",
      "|1500100001|施笑槐| 22| 女|文科六班|\n",
      "|1500100002|吕金鹏| 24| 男|文科七班|\n",
      "|1500100003|单乐蕊| 22| 女|理科六班|\n",
      "|1500100004|葛德曜| 24| 男|理科三班|\n",
      "|1500100005|宣谷芹| 22| 女|理科五班|\n",
      "|1500100006|边昂雄| 21| 男|理科二班|\n",
      "|1500100007|尚孤风| 23| 女|文科六班|\n",
      "|1500100008|符半双| 22| 女|理科六班|\n",
      "|1500100009|沈德昌| 21| 男|理科一班|\n",
      "|1500100010|羿彦昌| 23| 男|理科六班|\n",
      "|1500100011|宰运华| 21| 男|理科三班|\n",
      "|1500100012|梁易槐| 21| 女|理科一班|\n",
      "|1500100013|逯君昊| 24| 男|文科二班|\n",
      "|1500100014|羿旭炎| 23| 男|理科五班|\n",
      "|1500100015|宦怀绿| 21| 女|理科一班|\n",
      "|1500100016|潘访烟| 23| 女|文科一班|\n",
      "|1500100017|高芷天| 21| 女|理科五班|\n",
      "|1500100018|骆怜雪| 21| 女|文科六班|\n",
      "|1500100019|娄曦之| 24| 男|理科三班|\n",
      "|1500100020|杭振凯| 23| 男|理科四班|\n",
      "+----------+------+---+---+--------+\n",
      "only showing top 20 rows\n",
      "\n",
      "+----------+------+\n",
      "|        id|  name|\n",
      "+----------+------+\n",
      "|1500100001|施笑槐|\n",
      "|1500100002|吕金鹏|\n",
      "|1500100003|单乐蕊|\n",
      "|1500100004|葛德曜|\n",
      "|1500100005|宣谷芹|\n",
      "|1500100006|边昂雄|\n",
      "|1500100007|尚孤风|\n",
      "|1500100008|符半双|\n",
      "|1500100009|沈德昌|\n",
      "|1500100010|羿彦昌|\n",
      "+----------+------+\n",
      "only showing top 10 rows\n",
      "\n",
      "+----------+---+\n",
      "|        id|age|\n",
      "+----------+---+\n",
      "|1500100001|122|\n",
      "|1500100002|124|\n",
      "|1500100003|122|\n",
      "|1500100004|124|\n",
      "|1500100005|122|\n",
      "|1500100006|121|\n",
      "|1500100007|123|\n",
      "|1500100008|122|\n",
      "|1500100009|121|\n",
      "|1500100010|123|\n",
      "+----------+---+\n",
      "only showing top 10 rows\n",
      "\n",
      "+----------+---+\n",
      "|        id|age|\n",
      "+----------+---+\n",
      "|1500100001| 42|\n",
      "|1500100002| 44|\n",
      "|1500100003| 42|\n",
      "|1500100004| 44|\n",
      "|1500100005| 42|\n",
      "|1500100006| 41|\n",
      "|1500100007| 43|\n",
      "|1500100008| 42|\n",
      "|1500100009| 41|\n",
      "|1500100010| 43|\n",
      "+----------+---+\n",
      "only showing top 10 rows\n",
      "\n",
      "+----------+---+\n",
      "|        id|age|\n",
      "+----------+---+\n",
      "|1500100001|122|\n",
      "|1500100002|124|\n",
      "|1500100003|122|\n",
      "|1500100004|124|\n",
      "|1500100005|122|\n",
      "|1500100006|121|\n",
      "|1500100007|123|\n",
      "|1500100008|122|\n",
      "|1500100009|121|\n",
      "|1500100010|123|\n",
      "|1500100011|121|\n",
      "|1500100012|121|\n",
      "|1500100013|124|\n",
      "|1500100014|123|\n",
      "|1500100015|121|\n",
      "|1500100016|123|\n",
      "|1500100017|121|\n",
      "|1500100018|121|\n",
      "|1500100019|124|\n",
      "|1500100020|123|\n",
      "+----------+---+\n",
      "only showing top 20 rows\n",
      "\n",
      "+----------+---+\n",
      "|        id|age|\n",
      "+----------+---+\n",
      "|1500100001| 22|\n",
      "|1500100002| 24|\n",
      "|1500100003| 22|\n",
      "|1500100004| 24|\n",
      "|1500100005| 22|\n",
      "|1500100006| 21|\n",
      "|1500100007| 23|\n",
      "|1500100008| 22|\n",
      "|1500100009| 21|\n",
      "|1500100010| 23|\n",
      "|1500100011| 21|\n",
      "|1500100012| 21|\n",
      "|1500100013| 24|\n",
      "|1500100014| 23|\n",
      "|1500100015| 21|\n",
      "|1500100016| 23|\n",
      "|1500100017| 21|\n",
      "|1500100018| 21|\n",
      "|1500100019| 24|\n",
      "|1500100020| 23|\n",
      "+----------+---+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "execution_count": 21
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-16T03:13:18.967650Z",
     "start_time": "2025-09-16T03:13:18.807420Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 3、where\n",
    "students_df.where(abs(\"age\") > 23).show()\n",
    "students_df.where(substring(\"clazz\", 1, 2) == \"文科\").show()\n",
    "# 多条件\n",
    "students_df.where((substring(\"clazz\", 1, 2) == \"文科\") & (col(\"age\") > 23)).show()"
   ],
   "id": "906d12cd83436649",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------+---+---+--------+\n",
      "|        id|  name|age|sex|   clazz|\n",
      "+----------+------+---+---+--------+\n",
      "|1500100002|吕金鹏| 24| 男|文科七班|\n",
      "|1500100004|葛德曜| 24| 男|理科三班|\n",
      "|1500100013|逯君昊| 24| 男|文科二班|\n",
      "|1500100019|娄曦之| 24| 男|理科三班|\n",
      "|1500100028|幸浩邈| 24| 男|理科五班|\n",
      "|1500100031|麻智刚| 24| 男|文科六班|\n",
      "|1500100032|雍静枫| 24| 女|文科二班|\n",
      "|1500100033|桑昆峰| 24| 男|理科三班|\n",
      "|1500100034|薛鸿朗| 24| 男|理科五班|\n",
      "|1500100035|包瀚玥| 24| 男|理科四班|\n",
      "|1500100040|纪映之| 24| 女|文科四班|\n",
      "|1500100041|傅景天| 24| 男|理科四班|\n",
      "|1500100042|麻旭尧| 24| 男|文科四班|\n",
      "|1500100043|晏昆鹏| 24| 男|理科六班|\n",
      "|1500100044|田天风| 24| 女|文科二班|\n",
      "|1500100051|江寄容| 24| 女|文科五班|\n",
      "|1500100055|卫鸿熙| 24| 男|文科一班|\n",
      "|1500100057|经沛白| 24| 女|文科四班|\n",
      "|1500100059|席昌燎| 24| 男|理科二班|\n",
      "|1500100060|阮迎荷| 24| 女|文科四班|\n",
      "+----------+------+---+---+--------+\n",
      "only showing top 20 rows\n",
      "\n",
      "+----------+------+---+---+--------+\n",
      "|        id|  name|age|sex|   clazz|\n",
      "+----------+------+---+---+--------+\n",
      "|1500100001|施笑槐| 22| 女|文科六班|\n",
      "|1500100002|吕金鹏| 24| 男|文科七班|\n",
      "|1500100007|尚孤风| 23| 女|文科六班|\n",
      "|1500100013|逯君昊| 24| 男|文科二班|\n",
      "|1500100016|潘访烟| 23| 女|文科一班|\n",
      "|1500100018|骆怜雪| 21| 女|文科六班|\n",
      "|1500100022|薄运珧| 23| 男|文科四班|\n",
      "|1500100024|湛慕卉| 22| 女|文科二班|\n",
      "|1500100025|翁飞昂| 22| 男|文科四班|\n",
      "|1500100027|路辰锟| 21| 男|文科四班|\n",
      "|1500100031|麻智刚| 24| 男|文科六班|\n",
      "|1500100032|雍静枫| 24| 女|文科二班|\n",
      "|1500100036|阮旭炎| 22| 男|文科二班|\n",
      "|1500100037|顾向松| 22| 女|文科一班|\n",
      "|1500100039|窦孤风| 22| 女|文科六班|\n",
      "|1500100040|纪映之| 24| 女|文科四班|\n",
      "|1500100042|麻旭尧| 24| 男|文科四班|\n",
      "|1500100044|田天风| 24| 女|文科二班|\n",
      "|1500100047|秋旭尧| 23| 男|文科六班|\n",
      "|1500100050|方小蕾| 22| 女|文科五班|\n",
      "+----------+------+---+---+--------+\n",
      "only showing top 20 rows\n",
      "\n",
      "+----------+------+---+---+--------+\n",
      "|        id|  name|age|sex|   clazz|\n",
      "+----------+------+---+---+--------+\n",
      "|1500100002|吕金鹏| 24| 男|文科七班|\n",
      "|1500100013|逯君昊| 24| 男|文科二班|\n",
      "|1500100031|麻智刚| 24| 男|文科六班|\n",
      "|1500100032|雍静枫| 24| 女|文科二班|\n",
      "|1500100040|纪映之| 24| 女|文科四班|\n",
      "|1500100042|麻旭尧| 24| 男|文科四班|\n",
      "|1500100044|田天风| 24| 女|文科二班|\n",
      "|1500100051|江寄容| 24| 女|文科五班|\n",
      "|1500100055|卫鸿熙| 24| 男|文科一班|\n",
      "|1500100057|经沛白| 24| 女|文科四班|\n",
      "|1500100060|阮迎荷| 24| 女|文科四班|\n",
      "|1500100062|空芷荷| 24| 女|文科二班|\n",
      "|1500100063|申半双| 24| 女|文科四班|\n",
      "|1500100097|叶德昌| 24| 男|文科二班|\n",
      "|1500100102|洪天风| 24| 女|文科五班|\n",
      "|1500100109|从振强| 24| 男|文科三班|\n",
      "|1500100120|桓浩邈| 24| 男|文科三班|\n",
      "|1500100128|巫鸿哲| 24| 男|文科一班|\n",
      "|1500100139|左代萱| 24| 女|文科三班|\n",
      "|1500100140|郁运发| 24| 男|文科六班|\n",
      "+----------+------+---+---+--------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "execution_count": 31
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-16T03:17:57.372458Z",
     "start_time": "2025-09-16T03:17:56.993093Z"
    }
   },
   "cell_type": "code",
   "source": [
    "students_df.show()\n",
    "\n",
    "# 4、group by \n",
    "students_df \\\n",
    "    .groupBy(\"clazz\") \\\n",
    "    .agg(count(\"*\").alias(\"num\"), round(avg(\"age\"), 2).alias(\"avg_age\")) \\\n",
    "    .show()\n",
    "\n",
    "# 分组聚合之后可以直接过滤，类似having\n",
    "students_df \\\n",
    "    .groupBy(\"clazz\") \\\n",
    "    .agg(count(\"*\").alias(\"num\"), round(avg(\"age\"), 2).alias(\"avg_age\")) \\\n",
    "    .where(col(\"num\") > 80) \\\n",
    "    .show()"
   ],
   "id": "82a82cd0d57613d8",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------+---+---+--------+\n",
      "|        id|  name|age|sex|   clazz|\n",
      "+----------+------+---+---+--------+\n",
      "|1500100001|施笑槐| 22| 女|文科六班|\n",
      "|1500100002|吕金鹏| 24| 男|文科七班|\n",
      "|1500100003|单乐蕊| 22| 女|理科六班|\n",
      "|1500100004|葛德曜| 24| 男|理科三班|\n",
      "|1500100005|宣谷芹| 22| 女|理科五班|\n",
      "|1500100006|边昂雄| 21| 男|理科二班|\n",
      "|1500100007|尚孤风| 23| 女|文科六班|\n",
      "|1500100008|符半双| 22| 女|理科六班|\n",
      "|1500100009|沈德昌| 21| 男|理科一班|\n",
      "|1500100010|羿彦昌| 23| 男|理科六班|\n",
      "|1500100011|宰运华| 21| 男|理科三班|\n",
      "|1500100012|梁易槐| 21| 女|理科一班|\n",
      "|1500100013|逯君昊| 24| 男|文科二班|\n",
      "|1500100014|羿旭炎| 23| 男|理科五班|\n",
      "|1500100015|宦怀绿| 21| 女|理科一班|\n",
      "|1500100016|潘访烟| 23| 女|文科一班|\n",
      "|1500100017|高芷天| 21| 女|理科五班|\n",
      "|1500100018|骆怜雪| 21| 女|文科六班|\n",
      "|1500100019|娄曦之| 24| 男|理科三班|\n",
      "|1500100020|杭振凯| 23| 男|理科四班|\n",
      "+----------+------+---+---+--------+\n",
      "only showing top 20 rows\n",
      "\n",
      "+--------+---+-------+\n",
      "|   clazz|num|avg_age|\n",
      "+--------+---+-------+\n",
      "|文科六班|103|  22.59|\n",
      "|文科七班|  1|   24.0|\n",
      "|理科六班| 91|  22.49|\n",
      "|理科三班| 68|  22.68|\n",
      "|理科五班| 70|  22.64|\n",
      "|理科二班| 79|  22.56|\n",
      "|理科一班| 78|  22.33|\n",
      "|文科二班| 87|  22.38|\n",
      "|文科一班| 72|  22.42|\n",
      "|理科四班| 92|  22.63|\n",
      "|文科四班| 81|  22.51|\n",
      "|文科五班| 84|  22.31|\n",
      "|文科三班| 94|  22.68|\n",
      "+--------+---+-------+\n",
      "\n",
      "+--------+---+-------+\n",
      "|   clazz|num|avg_age|\n",
      "+--------+---+-------+\n",
      "|文科六班|103|  22.59|\n",
      "|理科六班| 91|  22.49|\n",
      "|文科二班| 87|  22.38|\n",
      "|理科四班| 92|  22.63|\n",
      "|文科四班| 81|  22.51|\n",
      "|文科五班| 84|  22.31|\n",
      "|文科三班| 94|  22.68|\n",
      "+--------+---+-------+\n",
      "\n"
     ]
    }
   ],
   "execution_count": 36
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-16T03:22:52.836421Z",
     "start_time": "2025-09-16T03:22:52.690603Z"
    }
   },
   "cell_type": "code",
   "source": [
    "students_df.show()\n",
    "# 5、order by \n",
    "students_df \\\n",
    "    .groupBy(\"clazz\") \\\n",
    "    .agg(count(\"*\").alias(\"num\")) \\\n",
    "    .orderBy(col(\"clazz\").desc(), col(\"num\").desc()) \\\n",
    "    .show()"
   ],
   "id": "a4559af8862b9712",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------+---+---+--------+\n",
      "|        id|  name|age|sex|   clazz|\n",
      "+----------+------+---+---+--------+\n",
      "|1500100001|施笑槐| 22| 女|文科六班|\n",
      "|1500100002|吕金鹏| 24| 男|文科七班|\n",
      "|1500100003|单乐蕊| 22| 女|理科六班|\n",
      "|1500100004|葛德曜| 24| 男|理科三班|\n",
      "|1500100005|宣谷芹| 22| 女|理科五班|\n",
      "|1500100006|边昂雄| 21| 男|理科二班|\n",
      "|1500100007|尚孤风| 23| 女|文科六班|\n",
      "|1500100008|符半双| 22| 女|理科六班|\n",
      "|1500100009|沈德昌| 21| 男|理科一班|\n",
      "|1500100010|羿彦昌| 23| 男|理科六班|\n",
      "|1500100011|宰运华| 21| 男|理科三班|\n",
      "|1500100012|梁易槐| 21| 女|理科一班|\n",
      "|1500100013|逯君昊| 24| 男|文科二班|\n",
      "|1500100014|羿旭炎| 23| 男|理科五班|\n",
      "|1500100015|宦怀绿| 21| 女|理科一班|\n",
      "|1500100016|潘访烟| 23| 女|文科一班|\n",
      "|1500100017|高芷天| 21| 女|理科五班|\n",
      "|1500100018|骆怜雪| 21| 女|文科六班|\n",
      "|1500100019|娄曦之| 24| 男|理科三班|\n",
      "|1500100020|杭振凯| 23| 男|理科四班|\n",
      "+----------+------+---+---+--------+\n",
      "only showing top 20 rows\n",
      "\n",
      "+--------+---+\n",
      "|   clazz|num|\n",
      "+--------+---+\n",
      "|理科四班| 92|\n",
      "|理科六班| 91|\n",
      "|理科五班| 70|\n",
      "|理科二班| 79|\n",
      "|理科三班| 68|\n",
      "|理科一班| 78|\n",
      "|文科四班| 81|\n",
      "|文科六班|103|\n",
      "|文科五班| 84|\n",
      "|文科二班| 87|\n",
      "|文科三班| 94|\n",
      "|文科七班|  1|\n",
      "|文科一班| 72|\n",
      "+--------+---+\n",
      "\n"
     ]
    }
   ],
   "execution_count": 42
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-16T03:26:00.312307Z",
     "start_time": "2025-09-16T03:26:00.299302Z"
    }
   },
   "cell_type": "code",
   "source": [
    "\n",
    "scores_df = spark \\\n",
    "    .read \\\n",
    "    .format(\"csv\") \\\n",
    "    .option(\"sep\", \",\") \\\n",
    "    .schema(\"sid STRING,cid STRING,score DOUBLE\") \\\n",
    "    .load(\"../../data/score.txt\")"
   ],
   "id": "b4b9c484accee4d9",
   "outputs": [],
   "execution_count": 47
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-16T03:26:01.572043Z",
     "start_time": "2025-09-16T03:26:01.526836Z"
    }
   },
   "cell_type": "code",
   "source": "scores_df.show(2)",
   "id": "c5730bfbe71bdb6b",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+-------+-----+\n",
      "|       sid|    cid|score|\n",
      "+----------+-------+-----+\n",
      "|1500100001|1000001| 98.0|\n",
      "|1500100001|1000002|  5.0|\n",
      "+----------+-------+-----+\n",
      "only showing top 2 rows\n",
      "\n"
     ]
    }
   ],
   "execution_count": 48
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-16T03:32:39.265857Z",
     "start_time": "2025-09-16T03:32:38.863751Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 6、join\n",
    "\n",
    "# 关联字段名一样时\n",
    "# students_df.join(scores_df, on=\"id\", how=\"inner\").show()\n",
    "\n",
    "# 关联字段名不一样时\n",
    "students_df.join(scores_df, on=students_df.id == scores_df.sid, how=\"inner\").show()\n",
    "\n",
    "students_df.alias(\"a\").join(scores_df.alias(\"b\"), on=col(\"a.id\") == col(\"b.sid\"), how=\"inner\").show()\n",
    "\n",
    "students_df.alias(\"a\") \\\n",
    "    .join(scores_df.alias(\"b\"), on=col(\"a.id\") == col(\"b.sid\"), how=\"inner\") \\\n",
    "    .groupBy(\"id\", \"clazz\") \\\n",
    "    .agg(sum(\"score\").alias(\"sum_score\")) \\\n",
    "    .groupBy(\"clazz\") \\\n",
    "    .agg(round(avg(\"sum_score\")).alias(\"avg_score\")) \\\n",
    "    .show()"
   ],
   "id": "8fe88dcfa86ea04c",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------+---+---+--------+----------+-------+-----+\n",
      "|        id|  name|age|sex|   clazz|       sid|    cid|score|\n",
      "+----------+------+---+---+--------+----------+-------+-----+\n",
      "|1500100001|施笑槐| 22| 女|文科六班|1500100001|1000001| 98.0|\n",
      "|1500100001|施笑槐| 22| 女|文科六班|1500100001|1000002|  5.0|\n",
      "|1500100001|施笑槐| 22| 女|文科六班|1500100001|1000003|137.0|\n",
      "|1500100001|施笑槐| 22| 女|文科六班|1500100001|1000004| 29.0|\n",
      "|1500100001|施笑槐| 22| 女|文科六班|1500100001|1000005| 85.0|\n",
      "|1500100001|施笑槐| 22| 女|文科六班|1500100001|1000006| 52.0|\n",
      "|1500100002|吕金鹏| 24| 男|文科七班|1500100002|1000001|139.0|\n",
      "|1500100002|吕金鹏| 24| 男|文科七班|1500100002|1000002|102.0|\n",
      "|1500100002|吕金鹏| 24| 男|文科七班|1500100002|1000003| 44.0|\n",
      "|1500100002|吕金鹏| 24| 男|文科七班|1500100002|1000004| 18.0|\n",
      "|1500100002|吕金鹏| 24| 男|文科七班|1500100002|1000005| 46.0|\n",
      "|1500100002|吕金鹏| 24| 男|文科七班|1500100002|1000006| 91.0|\n",
      "|1500100003|单乐蕊| 22| 女|理科六班|1500100003|1000001| 48.0|\n",
      "|1500100003|单乐蕊| 22| 女|理科六班|1500100003|1000002|132.0|\n",
      "|1500100003|单乐蕊| 22| 女|理科六班|1500100003|1000003| 41.0|\n",
      "|1500100003|单乐蕊| 22| 女|理科六班|1500100003|1000007| 32.0|\n",
      "|1500100003|单乐蕊| 22| 女|理科六班|1500100003|1000008|  7.0|\n",
      "|1500100003|单乐蕊| 22| 女|理科六班|1500100003|1000009| 99.0|\n",
      "|1500100004|葛德曜| 24| 男|理科三班|1500100004|1000001|147.0|\n",
      "|1500100004|葛德曜| 24| 男|理科三班|1500100004|1000002| 69.0|\n",
      "+----------+------+---+---+--------+----------+-------+-----+\n",
      "only showing top 20 rows\n",
      "\n",
      "+----------+------+---+---+--------+----------+-------+-----+\n",
      "|        id|  name|age|sex|   clazz|       sid|    cid|score|\n",
      "+----------+------+---+---+--------+----------+-------+-----+\n",
      "|1500100001|施笑槐| 22| 女|文科六班|1500100001|1000001| 98.0|\n",
      "|1500100001|施笑槐| 22| 女|文科六班|1500100001|1000002|  5.0|\n",
      "|1500100001|施笑槐| 22| 女|文科六班|1500100001|1000003|137.0|\n",
      "|1500100001|施笑槐| 22| 女|文科六班|1500100001|1000004| 29.0|\n",
      "|1500100001|施笑槐| 22| 女|文科六班|1500100001|1000005| 85.0|\n",
      "|1500100001|施笑槐| 22| 女|文科六班|1500100001|1000006| 52.0|\n",
      "|1500100002|吕金鹏| 24| 男|文科七班|1500100002|1000001|139.0|\n",
      "|1500100002|吕金鹏| 24| 男|文科七班|1500100002|1000002|102.0|\n",
      "|1500100002|吕金鹏| 24| 男|文科七班|1500100002|1000003| 44.0|\n",
      "|1500100002|吕金鹏| 24| 男|文科七班|1500100002|1000004| 18.0|\n",
      "|1500100002|吕金鹏| 24| 男|文科七班|1500100002|1000005| 46.0|\n",
      "|1500100002|吕金鹏| 24| 男|文科七班|1500100002|1000006| 91.0|\n",
      "|1500100003|单乐蕊| 22| 女|理科六班|1500100003|1000001| 48.0|\n",
      "|1500100003|单乐蕊| 22| 女|理科六班|1500100003|1000002|132.0|\n",
      "|1500100003|单乐蕊| 22| 女|理科六班|1500100003|1000003| 41.0|\n",
      "|1500100003|单乐蕊| 22| 女|理科六班|1500100003|1000007| 32.0|\n",
      "|1500100003|单乐蕊| 22| 女|理科六班|1500100003|1000008|  7.0|\n",
      "|1500100003|单乐蕊| 22| 女|理科六班|1500100003|1000009| 99.0|\n",
      "|1500100004|葛德曜| 24| 男|理科三班|1500100004|1000001|147.0|\n",
      "|1500100004|葛德曜| 24| 男|理科三班|1500100004|1000002| 69.0|\n",
      "+----------+------+---+---+--------+----------+-------+-----+\n",
      "only showing top 20 rows\n",
      "\n",
      "+--------+---------+\n",
      "|   clazz|avg_score|\n",
      "+--------+---------+\n",
      "|文科六班|    370.0|\n",
      "|文科七班|    440.0|\n",
      "|理科六班|    367.0|\n",
      "|理科三班|    377.0|\n",
      "|理科五班|    387.0|\n",
      "|理科二班|    379.0|\n",
      "|理科一班|    357.0|\n",
      "|文科二班|    369.0|\n",
      "|文科一班|    378.0|\n",
      "|理科四班|    364.0|\n",
      "|文科四班|    380.0|\n",
      "|文科五班|    365.0|\n",
      "|文科三班|    384.0|\n",
      "+--------+---------+\n",
      "\n"
     ]
    }
   ],
   "execution_count": 61
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-16T06:14:19.465583Z",
     "start_time": "2025-09-16T06:14:19.461514Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 7、窗口函数\n",
    "# 1、排序类，row_number() rank()\n",
    "\n",
    "# 2、聚合类，sum max min avg count\n",
    "\n",
    "# 3、取值类，lag lead"
   ],
   "id": "678d4afa57ed134f",
   "outputs": [],
   "execution_count": 62
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-16T06:18:18.540925Z",
     "start_time": "2025-09-16T06:18:18.386045Z"
    }
   },
   "cell_type": "code",
   "source": [
    "\n",
    "burks_df = spark \\\n",
    "    .read \\\n",
    "    .format(\"csv\") \\\n",
    "    .option(\"sep\", \",\") \\\n",
    "    .schema(\n",
    "    \"burk STRING,year STRING,tsl01 DOUBLE,tsl02 DOUBLE,tsl03 DOUBLE,tsl04 DOUBLE,tsl05 DOUBLE,tsl06 DOUBLE,tsl07 DOUBLE,tsl08 DOUBLE,tsl09 DOUBLE,tsl10 DOUBLE,tsl11 DOUBLE,tsl12 DOUBLE\") \\\n",
    "    .load(\"../../data/burks.txt\")\n",
    "\n",
    "burks_df.createOrReplaceTempView(\"burk\")\n",
    "burks_df.show(2)"
   ],
   "id": "ba9aafc8911b7230",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+----+--------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+\n",
      "|  burk|year|   tsl01|  tsl02|  tsl03|  tsl04|  tsl05|  tsl06|  tsl07|  tsl08|  tsl09|  tsl10|  tsl11|  tsl12|\n",
      "+------+----+--------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+\n",
      "|853101|2010|100200.0|25002.0|19440.0|20550.0|14990.0|17227.0|40990.0|28778.0|19088.0|29889.0|10990.0|20990.0|\n",
      "|853101|2011| 19446.0|20556.0|14996.0|17233.0|40996.0|28784.0|19094.0|28779.0|19089.0|29890.0|10991.0|20991.0|\n",
      "+------+----+--------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+\n",
      "only showing top 2 rows\n",
      "\n"
     ]
    }
   ],
   "execution_count": 65
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-16T06:30:10.019628Z",
     "start_time": "2025-09-16T06:30:09.902413Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 1、统计每个公司每年按月累计收入\n",
    "# 输出结果\n",
    "# 公司代码,年度,月份,当月收入,累计收入\n",
    "# sum over用法\n",
    "# 1、排序：累加求和\n",
    "# 2、不排序: 全局求和\n",
    "spark.sql(\"\"\"\n",
    "select \n",
    " burk,\n",
    " year,\n",
    " month,\n",
    " tsl,\n",
    " sum(tsl) over(partition by burk,year order by month) as tsls\n",
    "from(\n",
    "    select \n",
    "        burk,\n",
    "        year,\n",
    "        index + 1 as month,\n",
    "        tsl\n",
    "    from burk\n",
    "    lateral view posexplode(array(tsl01,tsl02,tsl03,tsl04,tsl05,tsl06,tsl07,tsl08,tsl09,tsl10,tsl11,tsl12)) T as index,tsl\n",
    ") as a\n",
    "\"\"\").show(10)"
   ],
   "id": "885c546313462214",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+----+-----+--------+--------+\n",
      "|  burk|year|month|     tsl|    tsls|\n",
      "+------+----+-----+--------+--------+\n",
      "|846271|2010|    1|100210.0|100210.0|\n",
      "|846271|2010|    2| 25012.0|125222.0|\n",
      "|846271|2010|    3| 17237.0|142459.0|\n",
      "|846271|2010|    4| 41000.0|183459.0|\n",
      "|846271|2010|    5| 28788.0|212247.0|\n",
      "|846271|2010|    6| 19098.0|231345.0|\n",
      "|846271|2010|    7| 29899.0|261244.0|\n",
      "|846271|2010|    8| 28788.0|290032.0|\n",
      "|846271|2010|    9| 19098.0|309130.0|\n",
      "|846271|2010|   10| 29899.0|339029.0|\n",
      "+------+----+-----+--------+--------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "execution_count": 81
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-16T06:41:16.860844Z",
     "start_time": "2025-09-16T06:41:16.714888Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# DSL实现\n",
    "cols = array(\"tsl01\", \"tsl02\", \"tsl03\", \"tsl04\", \"tsl05\", \"tsl06\", \"tsl07\", \"tsl08\", \"tsl09\", \"tsl10\", \"tsl11\", \"tsl12\")\n",
    "\n",
    "# withColumn: 在DF的基础上增加列\n",
    "# drop: 删除多余的字段\n",
    "burks_df \\\n",
    "    .select(\"burk\", \"year\", posexplode(cols).alias(\"index\", \"tsl\")) \\\n",
    "    .withColumn(\"month\", col(\"index\") + 1) \\\n",
    "    .drop(\"index\") \\\n",
    "    .withColumn(\"tsls\", sum(\"tsl\").over(Window.partitionBy(\"burk\", \"year\").orderBy(\"month\"))) \\\n",
    "    .show(10)"
   ],
   "id": "518dd8d1a5660690",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+----+--------+-----+--------+\n",
      "|  burk|year|     tsl|month|    tsls|\n",
      "+------+----+--------+-----+--------+\n",
      "|846271|2010|100210.0|    1|100210.0|\n",
      "|846271|2010| 25012.0|    2|125222.0|\n",
      "|846271|2010| 17237.0|    3|142459.0|\n",
      "|846271|2010| 41000.0|    4|183459.0|\n",
      "|846271|2010| 28788.0|    5|212247.0|\n",
      "|846271|2010| 19098.0|    6|231345.0|\n",
      "|846271|2010| 29899.0|    7|261244.0|\n",
      "|846271|2010| 28788.0|    8|290032.0|\n",
      "|846271|2010| 19098.0|    9|309130.0|\n",
      "|846271|2010| 29899.0|   10|339029.0|\n",
      "+------+----+--------+-----+--------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "execution_count": 93
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-16T06:48:07.910157Z",
     "start_time": "2025-09-16T06:48:07.755160Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 2、统计每个公司当月比上年同期增长率\n",
    "# 公司代码,年度,月度,增长率（当月收入/上年当月收入 - 1）\n",
    "spark.sql(\"\"\"\n",
    "select \n",
    "    *,\n",
    "    nvl(round(tsl/last_tsl-1,5),1.0) as p \n",
    "from(\n",
    "    select \n",
    "         burk,\n",
    "         year,\n",
    "         month,\n",
    "         tsl,\n",
    "         lag(tsl,1,0) over(partition by burk,month order by year) last_tsl\n",
    "     from(\n",
    "        select \n",
    "            burk,\n",
    "            year,\n",
    "            index + 1 as month,\n",
    "            tsl\n",
    "        from burk\n",
    "        lateral view posexplode(array(tsl01,tsl02,tsl03,tsl04,tsl05,tsl06,tsl07,tsl08,tsl09,tsl10,tsl11,tsl12)) T as index,tsl\n",
    "    ) as a\n",
    ") as b\n",
    "\"\"\").show(20)\n"
   ],
   "id": "52afeb22bb8872a6",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+----+-----+--------+--------+-------+\n",
      "|  burk|year|month|     tsl|last_tsl|      p|\n",
      "+------+----+-----+--------+--------+-------+\n",
      "|846271|2010|    1|100210.0|     0.0|    1.0|\n",
      "|846271|2011|    1|100211.0|100210.0| 1.0E-5|\n",
      "|846271|2012|    1|100212.0|100211.0| 1.0E-5|\n",
      "|846271|2013|    1|100213.0|100212.0| 1.0E-5|\n",
      "|846271|2014|    1|100214.0|100213.0| 1.0E-5|\n",
      "|846271|2015|    1|100215.0|100214.0| 1.0E-5|\n",
      "|846271|2016|    1|100216.0|100215.0| 1.0E-5|\n",
      "|846271|2017|    1|100217.0|100216.0| 1.0E-5|\n",
      "|846271|2018|    1|100218.0|100217.0| 1.0E-5|\n",
      "|846271|2019|    1|100219.0|100218.0| 1.0E-5|\n",
      "|846271|2010|    2| 25012.0|     0.0|    1.0|\n",
      "|846271|2011|    2| 25013.0| 25012.0| 4.0E-5|\n",
      "|846271|2012|    2|100213.0| 25013.0|3.00644|\n",
      "|846271|2013|    2|100214.0|100213.0| 1.0E-5|\n",
      "|846271|2014|    2|100215.0|100214.0| 1.0E-5|\n",
      "|846271|2015|    2|100216.0|100215.0| 1.0E-5|\n",
      "|846271|2016|    2|100217.0|100216.0| 1.0E-5|\n",
      "|846271|2017|    2|100218.0|100217.0| 1.0E-5|\n",
      "|846271|2018|    2|100219.0|100218.0| 1.0E-5|\n",
      "|846271|2019|    2|100220.0|100219.0| 1.0E-5|\n",
      "+------+----+-----+--------+--------+-------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "execution_count": 102
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-16T06:54:29.404754Z",
     "start_time": "2025-09-16T06:54:29.240905Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# DSL实现\n",
    "# expr: 将常量转换成sql表达式\n",
    "# coalesce: 返回第一个不为null的列\n",
    "cols = array(\"tsl01\", \"tsl02\", \"tsl03\", \"tsl04\", \"tsl05\", \"tsl06\", \"tsl07\", \"tsl08\", \"tsl09\", \"tsl10\", \"tsl11\", \"tsl12\")\n",
    "burks_df \\\n",
    "    .select(\"burk\", \"year\", posexplode(cols).alias(\"index\", \"tsl\")) \\\n",
    "    .withColumn(\"month\", col(\"index\") + 1) \\\n",
    "    .drop(\"index\") \\\n",
    "    .withColumn(\"last_tsl\", lag(\"tsl\", 1, 0).over(Window.partitionBy(\"burk\", \"month\").orderBy(\"year\"))) \\\n",
    "    .withColumn(\"p\", coalesce(round(col(\"tsl\") / col(\"last_tsl\") - 1, 5), expr(\"1.0\"))) \\\n",
    "    .show()"
   ],
   "id": "6d5b532916184503",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+----+--------+-----+--------+-------+\n",
      "|  burk|year|     tsl|month|last_tsl|      p|\n",
      "+------+----+--------+-----+--------+-------+\n",
      "|846271|2010|100210.0|    1|     0.0|    1.0|\n",
      "|846271|2011|100211.0|    1|100210.0| 1.0E-5|\n",
      "|846271|2012|100212.0|    1|100211.0| 1.0E-5|\n",
      "|846271|2013|100213.0|    1|100212.0| 1.0E-5|\n",
      "|846271|2014|100214.0|    1|100213.0| 1.0E-5|\n",
      "|846271|2015|100215.0|    1|100214.0| 1.0E-5|\n",
      "|846271|2016|100216.0|    1|100215.0| 1.0E-5|\n",
      "|846271|2017|100217.0|    1|100216.0| 1.0E-5|\n",
      "|846271|2018|100218.0|    1|100217.0| 1.0E-5|\n",
      "|846271|2019|100219.0|    1|100218.0| 1.0E-5|\n",
      "|846271|2010| 25012.0|    2|     0.0|    1.0|\n",
      "|846271|2011| 25013.0|    2| 25012.0| 4.0E-5|\n",
      "|846271|2012|100213.0|    2| 25013.0|3.00644|\n",
      "|846271|2013|100214.0|    2|100213.0| 1.0E-5|\n",
      "|846271|2014|100215.0|    2|100214.0| 1.0E-5|\n",
      "|846271|2015|100216.0|    2|100215.0| 1.0E-5|\n",
      "|846271|2016|100217.0|    2|100216.0| 1.0E-5|\n",
      "|846271|2017|100218.0|    2|100217.0| 1.0E-5|\n",
      "|846271|2018|100219.0|    2|100218.0| 1.0E-5|\n",
      "|846271|2019|100220.0|    2|100219.0| 1.0E-5|\n",
      "+------+----+--------+-----+--------+-------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "execution_count": 109
  },
  {
   "metadata": {},
   "cell_type": "code",
   "outputs": [],
   "execution_count": null,
   "source": "",
   "id": "5f3db3a29730493a"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
